package org.lq.classinfo.dao.impl;

import lombok.extern.log4j.Log4j;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.lq.classinfo.dao.SyllabusInfoDao;
import org.lq.classinfo.entity.SyllabusInfo;
import org.lq.util.JDBCUtil;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 课程表数据访问层
 * @author 刘明昕
 * @created 2020-10-13 18:17
 */
@Log4j
public class SyllabusInfoDaoImpl implements SyllabusInfoDao {
    /**
     * 添加课程信息
     * @param syllabusInfo
     * @return
     */
    @Override
    public int save(SyllabusInfo syllabusInfo) {
        int num = 0;
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            num = queryRunner.update(
                    "insert into syllabus_info (syllabus_yi, syllabus_er, syllabus_san, syllabus_si, syllabus_wu, syllabus_liu, syllabus_qi, is_uesd, syllabus_name)" +
                            "values (?,?,?,?,?,?,?,?,?)",
                    syllabusInfo.getSyllabusYi(),
                    syllabusInfo.getSyllabusEr(),
                    syllabusInfo.getSyllabusSan(),
                    syllabusInfo.getSyllabusSi(),
                    syllabusInfo.getSyllabusWu(),
                    syllabusInfo.getSyllabusLiu(),
                    syllabusInfo.getSyllabusQi(),
                    syllabusInfo.getIsUesd(),
                    syllabusInfo.getSyllabusName());
        } catch (SQLException throwables) {
            log.error("添加课程出错!",throwables);
        }
        return num;
    }

    /**
     * 修改课程信息
     * @param syllabusInfo
     * @return
     */
    @Override
    public int update(SyllabusInfo syllabusInfo) {
        int num = 0;
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            num = queryRunner.update(
                    "update syllabus_info set syllabus_yi = ?,syllabus_er = ?, syllabus_san = ?, syllabus_si = ?," +
                            "syllabus_wu = ?,syllabus_liu = ?,syllabus_qi = ?,is_uesd = ?,syllabus_name = ? where syllabus_id = ?",
                    syllabusInfo.getSyllabusYi(),
                    syllabusInfo.getSyllabusEr(),
                    syllabusInfo.getSyllabusSan(),
                    syllabusInfo.getSyllabusSi(),
                    syllabusInfo.getSyllabusWu(),
                    syllabusInfo.getSyllabusLiu(),
                    syllabusInfo.getSyllabusQi(),
                    syllabusInfo.getIsUesd(),
                    syllabusInfo.getSyllabusName(),
                    syllabusInfo.getSyllabusId());
        } catch (SQLException throwables) {
            log.error("修改课程出错!",throwables);
        }
        return num;
    }

    /**
     * 删除课程信息
     * @param id
     * @return
     */
    @Override
    public int delete(int id) {
        int num = 0;
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            num = queryRunner.update("delete from syllabus_info where syllabus_id = ?",id);
        } catch (SQLException throwables) {
            log.error("删除课程出错!",throwables);
        }
        return num;
    }

    /**
     * 根据id查询课程信息
     * @param id
     * @return
     */
    @Override
    public SyllabusInfo getById(int id) {
        SyllabusInfo syllabusInfo = null;
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            syllabusInfo = queryRunner.query("select * from v_syllabus where syllabusId = ?",new BeanHandler<SyllabusInfo>(SyllabusInfo.class),id);
        } catch (SQLException throwables) {
            log.error("根据id查询课程出错!",throwables);
        }
        return syllabusInfo;
    }

    /**
     * 获取数据总行数
     * @return
     */
    @Override
    public int getCount() {
        int num = 0;
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            num = queryRunner.query("select count(1) from v_syllabus",new ScalarHandler<Long>()).intValue();
        } catch (SQLException throwables) {
            log.error("获取课程数据总行数出错!",throwables);
        }
        return num;
    }

    /**
     * 分页
     * @param startIndex
     * @param pageSize
     * @return
     */
    @Override
    public List<SyllabusInfo> pageList(int startIndex, int pageSize) {
        List<SyllabusInfo> list = new ArrayList<>();
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            list = queryRunner.query("select * from v_syllabus limit ?,?",new BeanListHandler<>(SyllabusInfo.class),startIndex,pageSize);
        } catch (SQLException throwables) {
            log.error("获取课程分页数据出错!",throwables);
        }
        return list;
    }

    /**
     * 模糊查询总行数
     * @param values
     * @return
     */
    @Override
    public int getCount(String... values) {
        String val = "%" + values[0] + "%";
        int num = 0;
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            num = queryRunner.query("select count(1) from v_syllabus where syllabusName like ?",new ScalarHandler<Long>(),val).intValue();
        } catch (SQLException throwables) {
            log.error("模糊查询获取课程数据总行数出错!",throwables);
        }
        return num;
    }

    /**
     * 模糊查询分页数据
     * @param startIndex
     * @param pageSize
     * @param value
     * @return
     */
    @Override
    public List<SyllabusInfo> pageByValues(int startIndex, int pageSize, String... value) {
        String val = "%" + value[0] + "%";
        List<SyllabusInfo> list = new ArrayList<>();
        QueryRunner queryRunner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            list = queryRunner.query("select * from v_syllabus where syllabusName like ? limit ?,?",new BeanListHandler<>(SyllabusInfo.class),val,startIndex,pageSize);
        } catch (SQLException throwables) {
            log.error("模糊查询获取课程分页数据出错!",throwables);
        }
        return list;
    }
}
